#Include "Fileio.ch"
#Include "Topconn.ch"
#Include "tbiconn.ch"

/*
+----------------------------------------------------------------------------+
!                             FICHA TECNICA DO PROGRAMA                      !
+----------------------------------------------------------------------------+
!Programa          ! CPL02DMS                                                !
+------------------+---------------------------------------------------------+
!Descricao         ! DMS                                                     !
!                  ! ImportQuota.                                            !
!                  ! Importacao metas de venda por vendedor                  !
!                  !                                                         !
+------------------+---------------------------------------------------------+
!Autor             ! TSC505 - Felipe Jose Limas                              !
+------------------+---------------------------------------------------------+
!Data de Criacao   ! 31/05/12                                                !
+------------------+---------------------------------------------------------+
!   ATUALIZACOES                                                             !
+-------------------------------------------+-----------+-----------+--------+
!   Descricao detalhada da atualizacao      !Nome do    ! Analista  !Data da !
!                                           !Solicitante! Respons.  !Atualiz.!
+-------------------------------------------+-----------+-----------+--------+
!                                           !           !           !        !
!                                           !           !           !        !
+-------------------------------------------+-----------+-----------+--------+
!                                           !           !           !        !
!                                           !           !           !        !
+-------------------------------------------+-----------+-----------+--------+
*/

User Function CPL02DMS()
Local nHandle :=-1
Local cLinha  :=""
Local cQuery  :=""
Local cAli    :=GetNextAlias()
Local cAli1    :=GetNextAlias()
Local cArq    :=StrTran("\dms\gerados\ImportQuota_"+DtoS(dDataBase)+Time()+".txt", ":", "")

//campos do arquivo
Local cdQuota          :=""
Local nrMonth          :=""
Local nrYear           :=""
Local cdRegion         :=""
Local cdClassification :=""
Local vlQuota          :=""
Local vlQuotaLT        :=""
Local cdClientPotencial:=""
Local cdStatus         :=""
Local cdEan            :=""
Local cdBusinessType   :=""
Local cdSKU            :=""
Local nTotal           :=0
Local nQtdR            :=0
Local nQtdT            :=0

//metas dos vendedores
cQuery:= " SELECT ZA0_DATA,ZA0_COD,ZA0_META,ZA0_MSBLQL "
cQuery+= " FROM "+RetSqlName("ZA0")+" ZA0 "
cQuery+= " WHERE  "+RetSqlCond("ZA0") "
cQuery+= " AND DATEPART   ( Month   , ZA0_DATA ) ="+Alltrim(Substr(DtoS(dDataBase), 5,2))+" "
cQuery+= " AND DATEPART   ( YEAR , ZA0_DATA ) ="+Alltrim(Left(DtoS(dDataBase), 4))+" "
cQuery+= " AND ZA0_ALTDMS = 'S' "

TCQuery ChangeQuery(cQuery) New Alias &(cAli1)
//Conta qtd de registros
Count to nQtdT
If nQtdT > 0
	TcSqlExec("UPDATE "+RetSqlName("ZA0")+" SET ZA0_ALTDMS = 'S' ")
EndIf		

//metas dos vendedores
cQuery:= " SELECT ZA0_DATA,ZA0_COD,ZA0_META,ZA0_MSBLQL "
cQuery+= " FROM "+RetSqlName("ZA0")+" ZA0 "
cQuery+= " WHERE  "+RetSqlCond("ZA0") "
cQuery+= " AND DATEPART   ( Month   , ZA0_DATA ) ="+Alltrim(Substr(DtoS(dDataBase), 5,2))+" "
cQuery+= " AND DATEPART   ( YEAR , ZA0_DATA ) ="+Alltrim(Left(DtoS(dDataBase), 4))+" "
cQuery+= " AND ZA0_ALTDMS = 'S' "

TCQuery ChangeQuery(cQuery) New Alias &(cAli)

//Conta qtd de registros
Count to nQtdR
If nQtdR > 0
	nHandle:= FCREATE(cArq)
	If nHandle == -1
		conout("Erro ao criar arquivo - ferror " + Str(Ferror()))
	Else
		&(cAli)->(dbGoTop())
		Do While !&(cAli)->(EOF())
			cdQuota          := "KPI_MENSAL_VENDAS_NIV_LT"+Chr(9)
			nrMonth          := Alltrim(Substr(&(cAli)->(ZA0_DATA), 5,2))+Chr(9)
			nrYear           := Alltrim(Left(&(cAli)->(ZA0_DATA), 4))+Chr(9)
			cdRegion         := Alltrim(&(cAli)->(ZA0_COD))+Chr(9)
			cdClassification := "ICE10001"+Chr(9)
			vlQuota          := StrTran(StrTran(Alltrim(Transform(&(cAli)->(ZA0_META), "@e 999,999,999.99")), '.', ''), ',', '.')+Chr(9)
			nTotal           :=nTotal  +  &(cAli)->(ZA0_META)
			vlQuotaLT        := StrTran(StrTran(Alltrim(Transform(0, "@e 999,999,999.99")), '.', ''), ',', '.')+Chr(9)
			cdClientPotencial:= ""+Chr(9)
			cdStatus         := IIF(Alltrim(&(cAli)->(ZA0_MSBLQL)) == "1", "DEACT", "ACT")+Chr(9)
			cdEan            := ""+Chr(9)
			cdBusinessType   := ""+Chr(9)
			cdSKU            := ""+Chr(9)
			cLinha:= cdQuota+nrMonth+nrYear+cdRegion+cdClassification+vlQuota+vlQuotaLT+cdClientPotencial+cdStatus+cdEan+cdBusinessType+cdSKU
			FWrite(nHandle, cLinha+Chr(13)+Chr(10))
			&(cAli)->(dbSkip())
		EndDo
		cdQuota          := "KPI_MENSAL_VENDAS_NIV_LT"+Chr(9)
		nrMonth          := nrMonth
		nrYear           := nrYear
		cdRegion         := "MACRO"+Chr(9)
		cdClassification := "ICE10001"+Chr(9)
		vlQuota          := StrTran(StrTran(Alltrim(Transform(nTotal, "@e 999,999,999.99")), '.', ''), ',', '.')+Chr(9)
		vlQuotaLT        := StrTran(StrTran(Alltrim(Transform(0, "@e 999,999,999.99")), '.', ''), ',', '.')+Chr(9)
		cdClientPotencial:= ""+Chr(9)
		cdStatus         := "ACT"+Chr(9)
		cdEan            := ""+Chr(9)
		cdBusinessType   := ""+Chr(9)
		cdSKU            := ""+Chr(9)
		cLinha:= cdQuota+nrMonth+nrYear+cdRegion+cdClassification+vlQuota+vlQuotaLT+cdClientPotencial+cdStatus+cdEan+cdBusinessType+cdSKU
		FWrite(nHandle, cLinha+Chr(13)+Chr(10))
		
		
		&(cAli)->(dbCloseArea())
		FClose(nHandle)

		TcSqlExec("UPDATE "+RetSqlName("ZA0")+" SET ZA0_ALTDMS = 'N' ")		
		
		/*Tenta se conectar ao servidor ftp na porta 21
		//conexao sera feita com programa externo ws_ftp - gabriel 17/10/2012
		If !FTPCONNECT("189.59.223.136" , 21 ,"FTP_UDB_CPL", "16Access97")
			conout("Nao foi possivel conectar")
			Return .F.
		EndIf
		
		If !FTPUPLOAD(cArq, "/FTP_UDB_CPL/Inbox/"+SubStr(cArq, At("IMPORT", UPPER(cArq))))
			conout("Nao foi possivel realizar o upload!!")
			Return .F.
		EndIf
		FTPDISCONNECT() 
		*/
	EndIf
EndIf
Return cArq